﻿-- -- -- Select * from ir_attachment ;
-- -- -- -- Function: trgfninsertbudget_lines()
-- -- -- 
-- -- -- -- DROP FUNCTION trgfninsertbudget_lines();
-- -- -- 
-- -- -- 
-- -- -- CREATE OR REPLACE FUNCTION trgfnattachemnt_updatedata_attached_insertupdate()
-- -- -- RETURNS trigger AS
-- -- -- 	$BODY$
-- -- -- 		Begin
-- -- -- 			if new.res_model='sale.order' and new.res_id is not null Then
-- -- -- 				Update 
-- -- -- 					sale_order 
-- -- -- 				Set
-- -- -- 					data_attached=(Select 
-- -- -- 								case when sum(case when q_attached is null or q_attached=False then 0 else 1 end +
-- -- -- 								case when q_attached_bm is null or q_attached_bm=False then 0 else 1 end +
-- -- -- 								case when q_attached_be is null or q_attached_be=False then 0 else 1 end +
-- -- -- 								case when q_attached_jm is null or q_attached_jm =False then 0 else 1 end +	
-- -- -- 								case when q_attached_je is null or q_attached_je=False then 0 else 1 end)>0 then True else false end
-- -- -- 							from ir_attachment
-- -- -- 							where 
-- -- -- 								res_model='sale.order' and res_id=new.res_id
-- -- -- 							group by
-- -- -- 								res_model,res_id)
-- -- -- 				where
-- -- -- 					id=new.res_id;
-- -- -- 					
-- -- -- 			end if;
-- -- -- 			Return NULL;
-- -- -- 		end;
-- -- -- 	$BODY$
-- -- -- LANGUAGE plpgsql VOLATILE
-- -- -- COST 100;
-- -- -- ALTER FUNCTION trgfnattachemnt_updatedata_attached_insertupdate() OWNER TO openerp;
-- 
-- CREATE TRIGGER trgupdate_and_insert_delete_attachment
-- AFTER INSERT or UPDATE or Delete
-- ON ir_attachment
-- FOR EACH ROW
-- EXECUTE PROCEDURE trgfnattachemnt_updatedata_attached_insertupdate();

CREATE OR REPLACE FUNCTION trgfnattachemnt_updatedata_attached_insertupdate()
 RETURNS trigger AS
	$BODY$
		Declare r_id integer;
			r_model varchar(120); --Name of model
			
			q_data boolean := False; -- Check quoation data attached 
			-- Quotation Budget
			q_b_e boolean := False;
			q_b_m boolean := False;
			-- Quotation Budget & Job Combine
			q_b_combine boolean := False;
			q_j_combine boolean := False;
			--Job Budget
			j_b_e boolean := False;
			j_b_m boolean := False;
			
			k_progress_sent boolean := False;
			k_progress_received boolean := False;
			k_contract_sent boolean := False;
			k_contract_received boolean := False;
			
			--Approved Quotation
			k_approved_quotation boolean :=False;
		Begin
--Check What the Attached change		
			if TG_OP='DELETE' then
				begin
					r_id=old.res_id;
					r_model=old.res_model;
					if r_model='sale.order' then
						if old.q_attached=True then
							q_data=True;
						end if;
						if old.q_attached_be=True then
							q_b_e=True;
						end if;
						if old.q_attached_bm=True then
							q_b_m=True;
						end if;
						if old.q_attached_je=True then
							j_b_e=True;
						end if;
						if old.q_attached_jm=True then
							j_b_m=True;
						end if;
						
						if old.q_attached_qcombine=True then
							q_b_combine = True;
						end if;
						
						if old.q_attached_jcombine=True then
							q_j_combine = True;
						end if;
					end if;
					if r_model='kdvn.payment.from.client' then
						if old.kdvn_pfc_progress_sent=True then
							k_progress_sent = True;
						end if;
						if old.kdvn_pfc_progress_received=True then
							k_progress_received = True;
						end if;
					end if;
					if r_model='kdvn.contract.client' then
						if old.kdvn_contract_contract_sent=True then
							k_contract_sent = True;
						end if;
						if old.kdvn_contract_contract_received=True then
							k_contract_received = True;
						end if;
						if old.kdvn_contract_approved_quotation=True then
							k_approved_quotation = True;
						end if;
					end if;
				end;
			end if;
			
			if TG_OP='INSERT' THEN
				begin
					r_id=new.res_id;
					r_model=new.res_model;
					if r_model='sale.order' then
						if new.q_attached=True then
							q_data=True;
						end if;
						
						if new.q_attached_be=True then
							q_b_e=True;
						end if;
						if new.q_attached_bm=True then
							q_b_m=True;
						end if;

						if new.q_attached_qcombine=True then
							q_b_combine = True;
						end if;
						
						if new.q_attached_jcombine=True then
							q_j_combine = True;
						end if;
						
						if new.q_attached_je=True then
							j_b_e=True;
						end if;
						if new.q_attached_jm=True then
							j_b_m=True;
						end if;
					end if;
					if r_model='kdvn.payment.from.client' then
						if new.kdvn_pfc_progress_sent=True then
							k_progress_sent = True;
						end if;
						if new.kdvn_pfc_progress_received=True then
							k_progress_received = True;
						end if;
					end if;
					if r_model='kdvn.contract.client' then
						if new.kdvn_contract_contract_sent=True then
							k_contract_sent = True;
						end if;
						if new.kdvn_contract_contract_received=True then
							k_contract_received = True;
						end if;
						
						if new.kdvn_contract_approved_quotation=True then
							k_approved_quotation = True;
						end if;
					end if;
				end;
			end if;
			
			if TG_OP='UPDATE' THEN
				begin
					r_id=new.res_id;
					r_model=new.res_model;
					if r_model='sale.order' then
						if new.q_attached<>old.q_attached then
							q_data=True;
						end if;
						
						if new.q_attached_be<>old.q_attached_be then
							q_b_e=True;
						end if;
						if new.q_attached_bm<>old.q_attached_bm then
							q_b_m=True;
						end if;

						if new.q_attached_qcombine<>old.q_attached_qcombine then
							q_b_combine = True;
						end if;
						
						if new.q_attached_jcombine<>old.q_attached_jcombine then
							q_j_combine = True;
						end if;
						
						if new.q_attached_je<>old.q_attached_je then
							j_b_e=True;
						end if;
						if new.q_attached_jm<>old.q_attached_jm then
							j_b_m=True;
						end if;
						
					end if;
					if r_model='kdvn.payment.from.client' then
						if new.kdvn_pfc_progress_sent<>old.kdvn_pfc_progress_sent then
							k_progress_sent = True;
						end if;
						if new.kdvn_pfc_progress_received<>old.kdvn_pfc_progress_received then
							k_progress_received = True;
						end if;
					end if;
					if r_model='kdvn.contract.client' then	
						if new.kdvn_contract_contract_sent<>old.kdvn_contract_contract_sent then
							k_contract_sent = True;
						end if;
						if new.kdvn_contract_contract_received<>old.kdvn_contract_contract_received then
							k_contract_received = True;
						end if;
						if old.kdvn_contract_approved_quotation<>new.kdvn_contract_approved_quotation then
							k_approved_quotation = True;
						end if;
					end if;
				end;
			end if;
			
			if r_model='sale.order' and r_id is not null Then
				if q_data then --Update status Quotation Attached
					Update 
						sale_order 
					Set
						q_attached=(Select 
								case when sum(case when q_attached is null or q_attached=False then 0 else 1 end)>0 then True else false end
							from ir_attachment
							where 
								res_model='sale.order' and res_id=r_id
							group by
								res_model,res_id)
						where
							id=r_id;
					
				end if;
				
				if q_b_e then --Update status Quotation Budget E
					Update 
						sale_order 
					Set
						q_attached_be=(Select 
								case when sum(case when q_attached_be is null or q_attached_be=False then 0 else 1 end)>0 then True else false end
							from ir_attachment
							where 
								res_model='sale.order' and res_id=r_id
							group by
								res_model,res_id)
						where
							id=r_id;
					
				end if;
				
				if q_b_m then --Update status Quotation Budget M
					Update 
						sale_order 
					Set
						q_attached_bm=(Select 
								case when sum(case when q_attached_bm is null or q_attached_bm=False then 0 else 1 end)>0 then True else false end
							from ir_attachment
							where 
								res_model='sale.order' and res_id=r_id
							group by
								res_model,res_id)
						where
							id=r_id;
				end if;

				if j_b_e then --Update status Job Budget E
					Update 
						sale_order 
					Set
						q_attached_je=(Select 
								case when sum(case when q_attached_je is null or q_attached_je=False then 0 else 1 end)>0 then True else false end
							from ir_attachment
							where 
								res_model='sale.order' and res_id=r_id
							group by
								res_model,res_id)
						where
							id=r_id;					
				end if;

				if j_b_m then --Update status Job Budget M
					Update 
						sale_order 
					Set
						q_attached_jm=(Select 
								case when sum(case when q_attached_jm is null or q_attached_jm=False then 0 else 1 end)>0 then True else false end
							from ir_attachment
							where 
								res_model='sale.order' and res_id=r_id
							group by
								res_model,res_id)
						where
							id=r_id;
					
				end if;

-- 				if q_b_combine then
					Update 
						sale_order 
					Set
						q_attached_qcombine=(Select 
								case when sum(case when q_attached_qcombine is null or q_attached_qcombine=False then 0 else 1 end)>0 then True else false end
							from ir_attachment
							where 
								res_model='sale.order' and res_id=r_id
							group by
								res_model,res_id)
						where
							id=r_id;
				end if;	

				if q_j_combine then
					Update 
						sale_order 
					Set
						q_attached_jcombine=(Select 
								case when sum(case when q_attached_jcombine is null or q_attached_jcombine=False then 0 else 1 end)>0 then True else false end
							from ir_attachment
							where 
								res_model='sale.order' and res_id=r_id
							group by
								res_model,res_id)
						where
							id=r_id;
--				end if;
			elseif r_model='kdvn.payment.from.client' and r_id is not null Then
				if k_progress_sent then
					Update
						kdvn_payment_from_client
					Set
						attached_progress_sent=
						(Select 
							case when 
								sum(case when kdvn_pfc_progress_sent is null or kdvn_pfc_progress_sent=False 
									then 0 else 1 end)>0 then True else false end
						from ir_attachment
						where 
							res_model='kdvn.payment.from.client' and res_id=r_id
						group by
							res_model,res_id)
					where
						id=r_id;
				end if;
				
				if k_progress_received then
					Update
						kdvn_payment_from_client
					Set
						attached_progress_received=
						(Select 
							case when 
								sum(case when kdvn_pfc_progress_received is null or kdvn_pfc_progress_received=False 
									then 0 else 1 end)>0 then True else false end
						from ir_attachment
						where 
							res_model='kdvn.payment.from.client' and res_id=r_id
						group by
							res_model,res_id)
					where
						id=r_id;
				end if;
			elseif r_model='kdvn.contract.client' and r_id is not null Then
				if k_contract_sent then
					Update
						kdvn_contract_client
					Set
						attached_contract_sent=
						(Select 
							case when 
								sum(case when kdvn_contract_contract_sent is null or kdvn_contract_contract_sent=False 
									then 0 else 1 end)>0 then True else false end
						from ir_attachment
						where 
							res_model='kdvn.contract.client' and res_id=r_id
						group by
							res_model,res_id)
					where
						id=r_id;
				end if;
				
				if k_contract_received then
					Update
						kdvn_contract_client
					Set
						attached_contract_received=
						(Select 
							case when 
								sum(case when kdvn_contract_contract_received is null or kdvn_contract_contract_received=False 
									then 0 else 1 end)>0 then True else false end
						from ir_attachment
						where 
							res_model='kdvn.contract.client' and res_id=r_id
						group by
							res_model,res_id)
					where
						id=r_id;
				end if;

				if k_approved_quotation then
					Update
						kdvn_contract_client
					Set
						attached_approved_quotation=
						(Select 
							case when 
								sum(case when kdvn_contract_approved_quotation is null or kdvn_contract_approved_quotation=False 
									then 0 else 1 end)>0 then True else false end
						from ir_attachment
						where 
							res_model='kdvn.contract.client' and res_id=r_id
						group by
							res_model,res_id)
					where
						id=r_id;
				end if;
				
				
			end if;
		if TG_OP='DELETE' then
			Return old;
		else
			return new;
		end if;
		end;
	$BODY$
 LANGUAGE plpgsql VOLATILE
 COST 100;


